//引入mySql
const { query } = require('../models/mySql')
//路由
const router = require('koa-router')()
// 全局异常
const initdata = require('../utils/init')
//引入token生成函数
const { jwtSign } = require('../utils/token')
//转化年月日格式
const { conversionData } = require('../utils/time')
//随机生成不重复数字
const { generateRandomNumbers } = require('../utils/time')
//打印
const { log } = console

// 增 INSERT into 表名 (字段名列表(逗号隔开)) values(值列表(逗号隔开));
router.post('/increase', async (ctx, next) => {
  let { username, password } = ctx.request.body
  // 校验
  if (username === '' | password === '') {
    new initdata(ctx).tips('用户名和密码不能为空', 202)
    return false
  }
  if (username === undefined | password === undefined) {
    new initdata(ctx).tips('参数填写不正确', 400)
    return false
  }
  // 用户名校验
  let phone = /^1[3456789]\d{9}$/
  if (!phone.test(username)) {
    new initdata(ctx).tips('手机号码不正确', 202)
    return false
  }
  // 密码校验
  let reg = /^(?![\d]+$)(?![a-zA-Z]+$)(?![^\da-zA-Z]+$).{6,20}$/
  if (!reg.test(password)) {
    new initdata(ctx).tips('密码必须由6~20位数字和字母的组合', 202)
    return false
  }
  // 用引入的jwtSign方法生成token并返回
  const token = jwtSign({})
  // 新增sql
  let sql = 'INSERT into `user` (id,token,username,password) value(null,?,?,?);';
  let params = [token, username, password]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202)
  } else {
    ctx.body = {
      code: 200,
      message: 'success'
    }
  }
})
// 无规则新增
router.post('/register', async (ctx, next) => {
  let { username, password } = ctx.request.body
  // 存储到数据库
  let sql = 'INSERT into `user` (id,username,password) value(null,?,?);';
  let params = [username, password]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202)
  } else {
    ctx.body = {
      code: 200,
      message: '新增成功'
    }
  }
})

// 删 DELETE FROM 表名 WHERE 删除条件;
router.post('/delete', async (ctx, next) => {
  let { username, password } = ctx.request.body
  // 校验
  if (username === '' | password === '') {
    new initdata(ctx).tips('用户名和密码不能为空', 202)
    return false
  }
  // 删除sql
  let sql = 'DELETE FROM user WHERE username=?';
  let params = [username]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('删除失败', 202)
  } else {
    ctx.body = {
      code: 200,
      message: 'success'
    }
  }
})

// 改 UPDATE 表名 SET 字段1=表达式1,字段2=表达式2 WHERE 修改条件;
router.post('/change', async (ctx, next) => {
  let { username, password, id } = ctx.request.body
  // 校验
  if (username === '' | password === '') {
    new initdata(ctx).tips('用户名和密码不能为空', 202)
    return false
  }
  // 删除sql
  let sql = 'UPDATE user SET username = ?,password = ? WHERE id = ?';
  let params = [username, password, id]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('更新用户失败', 400)
  } else {
    ctx.body = {
      code: 200,
      message: 'success'
    }
  }
})

// 查 `SELECT * FROM 表名 WHERE 字段 = '${参数}'`
// 登录
router.post('/login', async (ctx, next) => {
  let { username, password } = ctx.request.body
  // 校验
  if (username === '' | password === '') {
    new initdata(ctx).tips('用户名和密码不能为空', 202, null, -4)
    return false
  }
  if (username === undefined | password === undefined) {
    new initdata(ctx).tips('参数填写不正确', 400, null, -4)
    return false
  }
  if (username !== 'admin' || password !== 'admin') {
    new initdata(ctx).tips('参数填写不正确', 202, null, -4)
    return false
  }
  if (username !== 'admin' && password !== 'admin') {
    // 用户名校验
    let phone = /^1[3456789]\d{9}$/
    if (!phone.test(username)) {
      new initdata(ctx).tips('手机号码不正确', 202, null, -4)
      return false
    }
    // 密码校验
    let reg = /^(?![\d]+$)(?![a-zA-Z]+$)(?![^\da-zA-Z]+$).{6,20}$/
    if (!reg.test(password)) {
      new initdata(ctx).tips('密码必须由6~20位数字和字母的组合', 202, null, -4)
      return false
    }
  }
  // 查询集合
  let sql = `SELECT * FROM user WHERE username = '${username}'`
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202, null, -4)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[0].token,
        total: 1
      },
      message: 'success'
    }
  }
})
router.get('/info', async (ctx, next) => {
  let { token } = ctx.query
  // 校验
  if (token === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM user WHERE token = '${token}'`
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: token,
        total: 1
      },
      message: 'success'
    }
  }
})
// 退出
router.post('/logout', async (ctx, next) => {
  // 返回
  ctx.body = {
    code: 200,
    data: null,
    message: 'success'
  }
})
// 用户权限
router.get('/securityuser', async (ctx, next) => {
  // 查询集合
  let listdata = require('../json/securityuser.json')
  ctx.body = {
    code: 200,
    data: {
      pageItems: listdata,
      total: 1
    },
    message: 'success'
  }
})
// 所有权限
router.get('/treemodules', async (ctx, next) => {
  // 查询集合
  let listdata = require('../json/treemodules.json')
  // 返回
  ctx.body = {
    code: 200,
    data: {
      pageItems: listdata,
      total: 1
    },
    message: 'success'
  }
})
// 常量表
router.get('/getAllConstant', async (ctx, next) => {
  // 查询集合
  let listdata = require('../json/constant.json')
  // 返回
  ctx.body = {
    code: 200,
    data: {
      pageItems: listdata,
      total: 1
    },
    message: 'success'
  }
})
// -----------------------首页--------------------------------------
// 代办
router.get('/getCartonTaskToDo', async (ctx, next) => {
  let { op_id } = ctx.query
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('op_id不能为空', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM agent`
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})

// 统计
// 折线图
router.get('/getCartonChart', async (ctx, next) => {
  let { op_id, sign } = ctx.query
  // 校验
  if (op_id === '' || sign === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getCartonChart') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  if (sign !== '1' && sign !== '2') {
    new initdata(ctx).tips('输入标志错误', 202)
    return false
  }
  // 查询集合
  let listdata = require('../json/getCartonChart.json')
  // 返回
  if (sign === '1') {
    let delivery = listdata[0].delivery.slice(0, 7)
    let loss = listdata[0].loss.slice(0, 7)
    let purchase = listdata[0].purchase.slice(0, 7)
    let order = listdata[0].order.slice(0, 7)
    ctx.body = {
      code: 200,
      data: {
        pageItems: [
          {
            delivery,
            loss,
            purchase,
            order,
          },
        ],
        total: listdata.length
      },
      message: 'success'
    }
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})
// 柱状图
router.get('/getCartonClientAmt', async (ctx, next) => {
  let { op_id, sign } = ctx.query
  // 校验
  if (op_id === '' || sign === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getCartonClientAmt') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  if (sign !== '1' && sign !== '2') {
    new initdata(ctx).tips('输入标志错误', 202)
    return false
  }
  // 查询集合
  let listdata = require('../json/getCartonClientAmt.json')
  // 返回
  if (sign === '1') {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[0],
        total: listdata[0].length
      },
      message: 'success'
    }
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[1],
        total: listdata[1].length
      },
      message: 'success'
    }
  }
})
// 饼图
router.get('/getCartonOrderType', async (ctx, next) => {
  let { op_id, sign } = ctx.query
  // 校验
  if (op_id === '' || sign === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getCartonOrderType') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  if (sign !== '1' && sign !== '2') {
    new initdata(ctx).tips('输入标志错误', 202)
    return false
  }
  // 查询集合
  let listdata = require('../json/getCartonOrderType.json')
  // 返回
  if (sign === '1') {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[0],
        total: listdata[0].length
      },
      message: 'success'
    }
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[1],
        total: listdata[1].length
      },
      message: 'success'
    }
  }
})
// 金额
router.get('/getCartonStatistics', async (ctx, next) => {
  let { op_id, sign } = ctx.query
  // 校验
  if (op_id === '' || sign === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getCartonStatistics') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  if (sign !== '1' && sign !== '2') {
    new initdata(ctx).tips('输入标志错误', 202)
    return false
  }
  // 查询集合
  let listdata = require('../json/getCartonStatistics.json')
  // 返回
  if (sign === '1') {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[0],
        total: listdata[0].length
      },
      message: 'success'
    }
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata[1],
        total: listdata[1].length
      },
      message: 'success'
    }
  }
})

// 通知
router.get('/getNotice', async (ctx, next) => {
  let { op_id } = ctx.query
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getNotice') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM sys_notice`
  let listdata = await query(sql)
  listdata.forEach(e => {
    e.createDate = conversionData(e.createDate)
  })
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('数据错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})
router.get('/noticeDetail', async (ctx, next) => {
  let { op_id, noticeId } = ctx.query
  // 校验
  if (op_id === '' || noticeId === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'noticeDetail') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM sys_notice WHERE noticeId = '${noticeId}'`
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: [{
          content: listdata[0].content,
          title: listdata[0].title
        }],
        total: listdata.length
      },
      message: 'success'
    }
  }
})

// 图片
router.get('/getPicture', async (ctx, next) => {
  let { op_id } = ctx.query
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getPicture') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM all_picture `
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})

// 常用功能
router.get('/getCommonFunction', async (ctx, next) => {
  let { op_id } = ctx.query
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'getCommonFunction') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM common`
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('账号或密码错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})
router.post('/insertCommonFunction', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { moduleIdList } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  if (op_id !== 'insertCommonFunction') {
    new initdata(ctx).tips('输入id错误', 202)
    return false
  }
  // 查询集合
  let sql1 = 'DELETE FROM common';
  await query(sql1)
  let sql = 'INSERT into `common` (id,name,path,index1,index2,status,icon,title) value(null,?,?,?,?,?,?,?);';
  moduleIdList.forEach(async (e) => {
    let params = [e.name, e.path, e.index1, e.index2, e.status, e.icon, e.title]
    await query(sql, params)
  });
  ctx.body = {
    code: 200,
    data: null,
    message: 'success'
  }
})
// ---------------------基础资料-------------------------------------
// --------------集团--------------
// 增 
router.post('/insertGroup', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { groupName } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 生成不重复的数字，范围为 1-1000
  const groupCode = generateRandomNumbers(1, 1, 1000);
  // 新增集合
  let sql = 'INSERT into `bd_group` (groupName,groupCode) value(?,?);';
  let params = [groupName, groupCode]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: null,
      message: 'success'
    }
  }
})
// 删 
router.post('/deleteGroup', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { groupCode } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 查询集合 
  let sql = 'DELETE FROM bd_group WHERE groupCode=?';
  let params = [groupCode]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: null,
      message: 'success'
    }
  }
})
// 改 
router.post('/updateGroup', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { groupCode, groupName } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 查询集合
  let sql = 'UPDATE bd_group SET groupName = ? WHERE groupCode = ?';
  let params = [groupName, groupCode]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: null,
      message: 'success'
    }
  }
})
// 查
router.get('/getGroup', async (ctx, next) => {
  let { op_id } = ctx.query
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('op_id不能为空', 202)
    return false
  }
  // 查询集合
  let sql = `SELECT * FROM bd_group`
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})
//---------------组织--------------- 
// 增 
router.post('/insertOrgan', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { organName, groupId, businessType, tel,
    fax, address, shortName, shortCode, orderFax, financeFax, multiFile } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 生成不重复的数字，范围为 1-1000
  const organId = generateRandomNumbers(1, 1, 1000);
  // 查询集合
  let sqlGroup = `SELECT * FROM bd_organization WHERE groupId='${groupId}'`
  let groupList = await query(sqlGroup)
  // 查询集合
  let sql = 'INSERT into `bd_organization` (groupName,organName,groupId,organId,businessType,tel,fax,address,shortName,shortCode,orderFax,financeFax,multiFile) values(?,?,?,?,?,?,?,?,?,?,?,?,?);';
  let params = [groupList[0].groupName, organName, groupId, organId, businessType.join(','), tel,
    fax, address, shortName, shortCode, orderFax, financeFax, multiFile]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: null,
      message: 'success'
    }
  }
})
// 删 
router.post('/deleteOrgan', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { organId } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 查询集合 
  let sql = 'DELETE FROM bd_organization WHERE organId=?';
  let params = [organId]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: null,
      message: 'success'
    }
  }
})
// 改 
router.post('/updateOrgan', async (ctx, next) => {
  let { op_id } = ctx.request.query
  let { organName, groupId, businessType, tel,
    fax, address, shortName, shortCode, orderFax, financeFax, multiFile, organCode } = ctx.request.body
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('不能为空', 202)
    return false
  }
  // 查询集合
  let sqlGroup = `SELECT * FROM bd_organization WHERE groupId='${groupId}'`
  let groupList = await query(sqlGroup)
  // 修改集合
  let sql = 'UPDATE bd_organization SET groupName=?, organName = ?,groupId = ?,businessType=?,tel=?,fax=?,address=?,shortName=?,shortCode=?,orderFax=?,financeFax=?,multiFile=? WHERE organCode = ?';
  let params = [groupList[0].groupName, organName, groupId, businessType.join(','), tel,
    fax, address, shortName, shortCode, orderFax, financeFax, multiFile, organCode]
  let listdata = await query(sql, params)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: null,
      message: 'success'
    }
  }
})
// 查
router.get('/getOrgan', async (ctx, next) => {
  let { op_id, groupId } = ctx.query
  // 校验
  if (op_id === '') {
    new initdata(ctx).tips('op_id不能为空', 202)
    return false
  }
  // 查询集合
  let sql1 = `SELECT * FROM bd_organization`
  let sql2 = `SELECT * FROM bd_organization WHERE groupId='${groupId}'`
  let sql = groupId ? sql2 : sql1
  let listdata = await query(sql)
  // 返回
  if (listdata.length === 0) {
    new initdata(ctx).tips('错误', 202)
  } else {
    ctx.body = {
      code: 200,
      data: {
        pageItems: listdata,
        total: listdata.length
      },
      message: 'success'
    }
  }
})


module.exports = router